package ext.csc.component.numbergen.loader;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import ext.csc.component.numbergen.bean.RequestInfoContained;
import ext.csc.component.numbergen.connection.DBConn;
import ext.csc.component.numbergen.number.GeneratedNumber;
import ext.csc.component.numbergen.number.NumberInfoContained;

public class NumberLoader {
	private DataSource data;
	private NumberTranslated translator;
	private LoadReporter reporter;
	
	public NumberLoader(DataSource data, NumberTranslated translator){
		this.data = data;
		this.translator = translator;
		reporter = new LoadReporter();
	}
	
	public void load(){
		loadNumber();
	}

	private void loadNumber(){
		String gn_number = "";
		String gn_class = "";
		String gn_requestor = "";
		String gn_requestdatetime = "";
		String gn_flag = "";
		String gn_requestdesc = "";
		String gn_name = "";
		String gn_canceldesc = "";
		String gn_seq = "";
		
		DBConn conn = null;
		
		try {
			conn = new DBConn();
			
			for (int i = translator.getRowBegin(); i <= data.end(); i++) {
				gn_number = " No Data ";
				HashMap record = data.getRecordData(i);
				translator.translate(record);
				NumberInfoContained gnumber = translator.getNumberInfo();
				RequestInfoContained requestInfo = translator.getRequestInfo();

				gn_number = "'" + gnumber.getNumber() + "' ";
				gn_class = "'" + gnumber.getClasspath() + "' ";
				gn_requestor = "'" + requestInfo.getRequestor() + "'";
				gn_requestdatetime = "to_date('" + requestInfo.getDatetime() + "','yyyy-mm-dd hh24:mi:ss') ";
				gn_flag = gnumber.getFlag() + " ";
				gn_requestdesc = "'" + requestInfo.getRequestdesc() + "' ";
				gn_name = "'" + requestInfo.getObjectname() + "' ";
				gn_canceldesc = "'" + requestInfo.getCanceldesc() + " ' ";
				gn_seq = String.valueOf(gnumber.getSeq()) + " ";
				
				String sql;
				sql = "SELECT * FROM csc_generatednumber WHERE " + 
						GeneratedNumber.GENERATEDNUMBER_GN_CLASS + " = " + gn_class + " AND " + 
						GeneratedNumber.GENERATEDNUMBER_GN_SEQ + " = " + gn_seq; 
				
				System.out.println("registerOneNumberRemote=======" + sql);
				ResultSet rs = conn.executeQuery(sql);
				
				if(!rs.next()){
					sql = "INSERT INTO csc_generatednumber " + 
								"(" + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + "," + GeneratedNumber.GENERATEDNUMBER_GN_CLASS + "," + 
								GeneratedNumber.GENERATEDNUMBER_GN_REQUESTOR + "," + GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDATETIME + "," + 
								GeneratedNumber.GENERATEDNUMBER_GN_FLAG + "," + GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDESC + "," + 
								GeneratedNumber.GENERATEDNUMBER_GN_NAME + "," + GeneratedNumber.GENERATEDNUMBER_GN_CANCELDESC + "," + 
								GeneratedNumber.GENERATEDNUMBER_GN_SEQ + ") " +
								"VALUES(" + gn_number + "," + gn_class + "," + gn_requestor + "," + gn_requestdatetime + "," + gn_flag + "," +
											gn_requestdesc + "," + gn_name + "," + gn_canceldesc + "," + gn_seq + ")";
				}else{
					sql = "UPDATE csc_generatednumber SET " + 
							GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = " + gn_number + ", " + 
							GeneratedNumber.GENERATEDNUMBER_GN_REQUESTOR + " = " + gn_requestor + ", " + 
							GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDATETIME + " = " + gn_requestdatetime + ", " + 
							GeneratedNumber.GENERATEDNUMBER_GN_FLAG + " = " + gn_flag +  ", " + 
							GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDESC + " = " + gn_requestdesc +  ", " + 
							GeneratedNumber.GENERATEDNUMBER_GN_NAME + " = " + gn_name +  ", " + 
							GeneratedNumber.GENERATEDNUMBER_GN_CANCELDESC + " = " + gn_canceldesc +  " " + 
							" WHERE " + 
							GeneratedNumber.GENERATEDNUMBER_GN_CLASS + " = " + gn_class + " AND " + 
							GeneratedNumber.GENERATEDNUMBER_GN_SEQ + " = " + gn_seq; 
				}
				
				conn.executeUpdate(sql);
				conn.commit();
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			System.out
					.println("*** New Number Terminated!! ***  Number: "
							+ gn_number + " Failed!");
		}
	}
	
	public LoadReporter getReporter() {
		return reporter;
	}
}
